package com.sh.data.engine.domain.dataquality.utils;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Lists;
import com.sh.data.engine.common.enumDefinition.DSType;
import com.sh.data.engine.domain.dataquality.model.domain.DataQualityRuleTemplateDomain;
import com.sh.data.engine.domain.dataquality.model.domain.RuleCondition;
import com.sh.data.engine.repository.dao.dataquality.entity.DataQualityRuleEntity;
import org.apache.commons.lang3.StringUtils;

import java.math.BigDecimal;
import java.util.List;

/**
 * @author tinglan.ys
 * @description: 数据质量模块与离线开发数据质量需要用到的通用方法。
 * @date 2022/11/17 下午3:14
 */
public class DataQualityUtils {

    // 完整性约束
    public static final String WZXYS = "1";
    // 空值约束
    public static final String KZYS = "2";
    // 空值占比约束
    public static final String KZZBYS = "3";
    // 取值范围约束
    public static final String QZFWYS = "4";
    // 最大值约束
    public static final String ZDZYS = "5";
    // 最小值约束
    public static final String ZXZYS = "6";
    // 平均值约束
    public static final String PJZYS = "7";
    // 总值约束
    public static final String ZZYS = "8";
    // 长度约束
    public static final String CDYS = "9";
    // 代码长度约束
    public static final String DMCDYS = "10";
    // 正则表达式约束-身份证号验证
    public static final String ZZBDSYS = "11";
    // 日期值约束
    public static final String RQZYS = "12";
    // 枚举值约束
    public static final String MJZYS = "13";
    // 空字符串约束
    public static final String KZFCYS = "14";
    // 空值或空字符串约束
    public static final String KZHZFCYS = "15";
    // 特殊字符约束
    public static final String TSZFYS = "16";
    // 重复值约束
    public static final String CFZYS = "17";
    // 及时性约束-天级别
    public static final String JSXYST = "18";
    // 正则表达式约束-电话号码验证
    public static final String ZZBDSYSDH = "19";
    // 及时性约束-小时级别
    public static final String JSXYSX = "20";

    /**
     * @description : 根据不同的数据源 获取规则模版，替换tableName
     * @author : tinglan.ys
     * @date : 2022/11/17
     */
    public static String replaceTableParam(
        DataQualityRuleTemplateDomain template, String partition, String table, String storageType) {

        String sql = "";
        if (StringUtils.isBlank(template.getSqlTemplate())) {
            return sql;
        }
        // 根据不同的存储拿到不同的sql
        JSONObject jsonObject = JSON.parseObject(template.getSqlTemplate());
        String finalSql = String.valueOf(jsonObject.get(storageType));
        if (StringUtils.isBlank(finalSql)) {
            return sql;
        }

        if (StringUtils.isNotBlank(partition)) {
            sql =
                finalSql.replace(
                    "${table}", "(select ${field} from " + table + " where " + partition + ") t");
        } else {
            sql = finalSql.replace("${table}", table);
        }

        return sql;
    }

    /**
     * @description : 构造执行sql
     * @author : tinglan.ys
     * @date : 2022/11/17
     */
    public static String getExecSql(
        String field,
        String fieldType,
        String sqlTemp,
        String compareType,
        String condition,
        DataQualityRuleTemplateDomain template,
        String storageType,
        String templateFilter) {
        // 模板规则
        // 1 完整性约束 select count(distinct(${field}))/count(${field}) from ${table}
        // 2 空值约束 select count(1) from ${table}  where ${field} is null
        // 3 空值占比约束 select count(case when ${field} is null then 1 else null end)/count(1) from
        // ${table}
        // 4 取值范围约束 select concat(min(${field}),',',max(${field})) from ${table}
        // 5 极大值约束 select max(${field}) from ${table}
        // 6 极小值约束 select min(${field}) from ${table}
        // 7 平均值约束 select avg(${field}) from ${table}
        // 8 总值约束 select sum(${field}) from ${table}
        // 9 长度约束 select concat(min(length(${field})),',',max(length(${field}))) from ${table}
        // 10 代码长度约束 select count(1) from ${table} where length(${field}) != ${length}
        // 11 正则表达式约束-身份证号验证 select count(1) from ${table} where ${field} not rlike '(^$)' and
        // ${field} not rlike '(^[1-9][0-}$)'
        // 12 日期值约束  select count(1) from ${table} where ${field} ${condition} ${time}
        // 13 枚举值约束 select count(1) from ${table} where ${field} ${condition} (${enums})
        // 14 空字符串约束 select count(1) from ${table} where ${field} = ''
        // 15 空值或空字符串约束 select count(1) from ${table} where ${field} = '' or ${field} is null
        // 16 特殊字符约束 select count(1) from ${table} where ${rlike}
        // 17 重复值约束 select count(1) from (select ${field},count(1) from ${table} group by ${field}
        // 18 及时性约束-天级别 select count(1) from ${table} where
        // datediff(from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss'),`${field}`) >=1
        // 19 正则表达式约束-电话号码验证  select count(1) from ${table} where ${field} not rlike ''  and ${field}
        // not rlike ''
        // 20 及时性约束-小时级别 select count(1) from ${table} where
        // (hour(current_timestamp())-hour(`${field}`)+(datediff(from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss'), `${field}`))*24) >= 1

        String sql = sqlTemp;
        sql = sql.replace("${field}", field);

        // 自定义规则返回
        if (template == null) {
            return sql;
        }

        // 模板规则处理
        if (template.getId().toString().equals(DMCDYS)) {
            // 10 代码长度约束
            JSONArray condi = JSONArray.parseArray(condition);
            int codeLength = condi.getJSONObject(0).getIntValue("value");
            sql = sql.replace("${length}", String.valueOf(codeLength));
        } else if (template.getId().toString().equals(RQZYS)) {
            // 12 日期值约束
            JSONArray condi = JSONArray.parseArray(condition);
            String date = condi.getJSONObject(0).getString("value");
            String compare = getCompareStr(Integer.parseInt(compareType));
            sql = sql.replace("${time}", "'" + date + "'");
            sql = sql.replace("${condition}", compare);
        } else if (template.getId().toString().equals(MJZYS)) {
            // 13 枚举值约束
            JSONArray condi = JSONArray.parseArray(condition);
            String enums = condi.getJSONObject(0).getString("value");
            if ("string".equalsIgnoreCase(fieldType)
                || "varchar".equalsIgnoreCase(fieldType)
                || "char".equalsIgnoreCase(fieldType)) {
                List<String> list = Lists.newArrayList();
                for (String s : enums.split(",")) {
                    list.add(String.format("'%s'", s));
                }
                enums = StringUtils.join(list, ",");
            }
            String compare = getCompareStr(Integer.parseInt(compareType));
            sql = sql.replace("${enums}", enums);
            sql = sql.replace("${condition}", compare);
        } else if (template.getId().toString().equals(TSZFYS)) {
            // 16 特殊字符约束
            JSONArray condi = JSONArray.parseArray(condition);
            String specialChars = condi.getJSONObject(0).getString("value");
            if (storageType.equals(DSType.Hive.name())) {
                List<String> list = Lists.newArrayList();
                for (char specialChar : specialChars.toCharArray()) {
                    list.add(String.format("%s rlike '%s'", field, specialChar));
                }
                String sp = StringUtils.join(list, " and ");
                sql = sql.replace("${rlike}", sp);
            } else if (storageType.equals(DSType.PostgreSQL.name())) {
                List<String> list = Lists.newArrayList();
                for (char specialChar : specialChars.toCharArray()) {
                    list.add(String.format("%s like '%%%s%%'", field, specialChar));
                }
                String sp = StringUtils.join(list, " and ");
                sql = sql.replace("${like}", sp);
            } else if (storageType.equals(DSType.Sap.name())) {
                List<String> list = Lists.newArrayList();
                for (char specialChar : specialChars.toCharArray()) {
                    list.add(String.format("%s like '%%%s%%'", field, specialChar));
                }
                String sp = StringUtils.join(list, " and ");
                sql = sql.replace("${like}", sp);
            } else {
                List<String> list = Lists.newArrayList();
                for (char specialChar : specialChars.toCharArray()) {
                    list.add(String.format("%s like '%%%s%%'", field, specialChar));
                }
                String sp = StringUtils.join(list, " and ");
                sql = sql.replace("${like}", sp);
            }
        }

        // 流星提的新需求 sql后要拼接删选条件-_-! 到时候又是一堆bug
        if (StringUtils.isNotBlank(templateFilter)) {
            if (StringUtils.containsIgnoreCase(sql, "where")) {
                sql = sql + " and " + templateFilter;
            } else {
                sql = sql + " where " + templateFilter;
            }
        }
        return sql;
    }

    public static Boolean checkRule(DataQualityRuleEntity rule, String value) {
        if (StringUtils.isBlank(value)) {
            // 如果没有找到值 说明没有相关的数据直接通过校验
            return true;
        }
        boolean flag = false;
        if (rule.getRuleDiy().equals(1)) {
            // 自定义规则
            BigDecimal real = new BigDecimal(value);
            BigDecimal expect = new BigDecimal(rule.getRuleExpectValue());
            flag = compareRuleExpect(real, expect, Integer.parseInt(rule.getRuleCompareType()));
        } else {
            if (rule.getTemplateId().toString().equals(WZXYS)) {
                // 完整性约束
                BigDecimal real = new BigDecimal(value);
                BigDecimal expect = new BigDecimal(rule.getRuleExpectValue());
                flag = compareRuleExpect(real, expect, Integer.parseInt(rule.getRuleCompareType()));
            } else if (rule.getTemplateId().toString().equals(KZYS)) {
                // 空值约束
                BigDecimal expect = new BigDecimal(0);
                BigDecimal real = new BigDecimal(value);
                flag = real.compareTo(expect) == 0;
            } else if (rule.getTemplateId().toString().equals(KZZBYS)) {
                // 空值占比约束
                BigDecimal real = new BigDecimal(value);
                BigDecimal expect = new BigDecimal(rule.getRuleExpectValue());
                flag = compareRuleExpect(real, expect, Integer.parseInt(rule.getRuleCompareType()));
            } else if (rule.getTemplateId().toString().equals(QZFWYS)) {
                // 取值范围约束

                List<RuleCondition> ruleConditions =
                    JSONArray.parseArray(rule.getRuleCondition(), RuleCondition.class);
                String[] valueList = ruleConditions.get(0).getValue().split(",");

                // 条件可能有1个 也可能有两个
                String[] vals = value.split(",");
                BigDecimal realMin = new BigDecimal(vals[0]);
                BigDecimal realMax = new BigDecimal(vals[1]);
                if (valueList.length == 2) {
                    // 两个条件的情况
                    BigDecimal expect1 = new BigDecimal(valueList[0]);
                    BigDecimal expect2 = new BigDecimal(valueList[1]);
                    String[] compareTypeList = rule.getRuleCompareType().split(",");
                    int ruleCompareType1 = Integer.parseInt(compareTypeList[0]);
                    int ruleCompareType2 = Integer.parseInt(compareTypeList[1]);
                    Boolean flag1 = compareRuleExpect2(realMin, realMax, expect1, ruleCompareType1);
                    Boolean flag2 = compareRuleExpect2(realMin, realMax, expect2, ruleCompareType2);
                    flag = flag1 && flag2;
                } else {
                    // 一个条件的情况
                    BigDecimal expect1 = new BigDecimal(valueList[0]);
                    flag =
                        compareRuleExpect2(
                            realMin, realMax, expect1, Integer.parseInt(rule.getRuleCompareType()));
                }

            } else if (rule.getTemplateId().toString().equals(ZDZYS)) {
                // 最大值约束
                JSONArray condi = JSONArray.parseArray(rule.getRuleCondition());
                BigDecimal expect = condi.getJSONObject(0).getBigDecimal("value");
                BigDecimal real = new BigDecimal(value);
                flag = real.compareTo(expect) <= 0;
            } else if (rule.getTemplateId().toString().equals(ZXZYS)) {
                // 最小值约束
                JSONArray condi = JSONArray.parseArray(rule.getRuleCondition());
                BigDecimal expect = condi.getJSONObject(0).getBigDecimal("value");
                BigDecimal real = new BigDecimal(value);
                flag = real.compareTo(expect) >= 0;
            } else if (rule.getTemplateId().toString().equals(PJZYS)) {
                // 平均值约束
                JSONArray condi = JSONArray.parseArray(rule.getRuleCondition());
                BigDecimal expect = condi.getJSONObject(0).getBigDecimal("value");
                BigDecimal real = new BigDecimal(value);
                flag = compareRuleExpect(real, expect, Integer.parseInt(rule.getRuleCompareType()));
            } else if (rule.getTemplateId().toString().equals(ZZYS)) {
                // 总值约束
                JSONArray condi = JSONArray.parseArray(rule.getRuleCondition());
                BigDecimal expect = condi.getJSONObject(0).getBigDecimal("value");
                BigDecimal real = new BigDecimal(value);
                flag = compareRuleExpect(real, expect, Integer.parseInt(rule.getRuleCompareType()));
            } else if (rule.getTemplateId().toString().equals(CDYS)) {
                // 长度约束

                List<RuleCondition> ruleConditions =
                    JSONArray.parseArray(rule.getRuleCondition(), RuleCondition.class);
                String[] valueList = ruleConditions.get(0).getValue().split(",");

                // 条件可能有1个 也可能有两个
                String[] vals = value.split(",");
                BigDecimal real1 = new BigDecimal(vals[0]);
                BigDecimal real2 = new BigDecimal(vals[1]);

                if (valueList.length == 2) {
                    // 两个条件的情况
                    BigDecimal expect1 = new BigDecimal(valueList[0]);
                    BigDecimal expect2 = new BigDecimal(valueList[1]);
                    String[] compareTypeList = rule.getRuleCompareType().split(",");
                    int ruleCompareType1 = Integer.parseInt(compareTypeList[0]);
                    int ruleCompareType2 = Integer.parseInt(compareTypeList[1]);

                    Boolean flag1 = compareRuleExpect2(real1, real2, expect1, ruleCompareType1);
                    Boolean flag2 = compareRuleExpect2(real1, real2, expect2, ruleCompareType2);
                    flag = flag1 && flag2;
                } else {
                    // 一个条件的情况
                    BigDecimal expect1 = new BigDecimal(valueList[0]);
                    flag =
                        compareRuleExpect2(
                            real1, real2, expect1, Integer.parseInt(rule.getRuleCompareType()));
                }

            } else if (rule.getTemplateId().toString().equals(DMCDYS)) {
                // 代码长度约束
                BigDecimal expect = new BigDecimal(rule.getRuleExpectValue());
                BigDecimal real = new BigDecimal(value);
                flag = compareRuleExpect(real, expect, Integer.parseInt(rule.getRuleCompareType()));
            } else if (rule.getTemplateId().toString().equals(ZZBDSYS)) {
                // 正则表达式约束 - 身份证
                BigDecimal expect = new BigDecimal(0);
                BigDecimal real = new BigDecimal(value);
                flag = real.compareTo(expect) == 0;
            } else if (rule.getTemplateId().toString().equals(RQZYS)) {
                // 日期值约束
                BigDecimal expect = new BigDecimal(0);
                BigDecimal real = new BigDecimal(value);
                flag = real.compareTo(expect) == 0;
            } else if (rule.getTemplateId().toString().equals(MJZYS)) {
                // 枚举值约束
                BigDecimal expect = new BigDecimal(0);
                BigDecimal real = new BigDecimal(value);
                flag = real.compareTo(expect) == 0;
            } else if (rule.getTemplateId().toString().equals(KZFCYS)) {
                // 空字符串约束
                BigDecimal expect = new BigDecimal(0);
                BigDecimal real = new BigDecimal(value);
                flag = real.compareTo(expect) == 0;
            } else if (rule.getTemplateId().toString().equals(KZHZFCYS)) {
                // 空值或空字符串约束
                BigDecimal expect = new BigDecimal(0);
                BigDecimal real = new BigDecimal(value);
                flag = real.compareTo(expect) == 0;
            } else if (rule.getTemplateId().toString().equals(TSZFYS)) {
                // 特殊字符约束
                BigDecimal expect = new BigDecimal(0);
                BigDecimal real = new BigDecimal(value);
                if ("6".equals(rule.getRuleCompareType())) {
                    // 用户配置的属于，那么存在特殊字符返回true，否则false
                    // real.compareTo(expect) == 0 true表示不存在特殊字符 false表示存在特殊字符
                    flag = real.compareTo(expect) != 0;
                } else {
                    // 用户配置的不属于，那么存在特殊字符返回false，否则true
                    flag = real.compareTo(expect) == 0;
                }

            } else if (rule.getTemplateId().toString().equals(CFZYS)) {
                // 重复值约束
                BigDecimal expect = new BigDecimal(0);
                BigDecimal real = new BigDecimal(value);
                flag = real.compareTo(expect) == 0;
            } else if (rule.getTemplateId().toString().equals(JSXYST)
                || rule.getTemplateId().toString().equals(JSXYSX)) {
                // 及时性约束
                BigDecimal expect = new BigDecimal(0);
                BigDecimal real = new BigDecimal(value);
                flag = real.compareTo(expect) == 0;
            } else if (rule.getTemplateId().toString().equals(ZZBDSYSDH)) {
                // 正则表达式约束-电话号码验证
                // 重复值约束
                BigDecimal expect = new BigDecimal(0);
                BigDecimal real = new BigDecimal(value);
                flag = real.compareTo(expect) == 0;
            }
        }

        return flag;
    }

    public static boolean compareRuleExpect(BigDecimal real, BigDecimal expect, int ruleCompareType) {

        switch (ruleCompareType) {
            case 0:
                // 大于
                return real.compareTo(expect) == 1;
            case 1:
                // 大于等于
                return real.compareTo(expect) > -1;
            case 2:
                // 等于
                return real.compareTo(expect) == 0;
            case 3:
                // 小于
                return real.compareTo(expect) == -1;
            case 4:
                // 小于等于
                return real.compareTo(expect) < 1;
            case 5:
                // 不等于
                return real.compareTo(expect) != 0;
            case 6:
                // 属于
                break;
            case 7:
                // 不属于
                break;
            default:
        }

        return false;
    }

    public static boolean compareRuleExpect2(
        BigDecimal min, BigDecimal max, BigDecimal expect, int ruleCompareType) {
        // 针对长度约束和取值范围约束的比较逻辑
        switch (ruleCompareType) {
            case 0:
                // 大于
                return min.compareTo(expect) == 1;
            case 1:
                // 大于等于
                return min.compareTo(expect) > -1;
            case 2:
                // 等于 最大值和最小值都与expect相等 返回true
                return min.compareTo(expect) == 0 && max.compareTo(expect) == 0;
            case 3:
                // 小于
                return max.compareTo(expect) == -1;
            case 4:
                // 小于等于
                return max.compareTo(expect) < 1;
            case 5:
                // 不等于 最大值或最小值有一个不等于expect 返回true
                return min.compareTo(expect) != 0 || max.compareTo(expect) != 0;
            case 6:
                // 属于
                break;
            case 7:
                // 不属于
                break;
            default:
        }

        return false;
    }

    public static String getCompareStr(int compareType) {
        switch (compareType) {
            case 0:
                // 大于
                return ">";
            case 1:
                // 大于等于
                return ">=";
            case 2:
                // 等于
                return "=";
            case 3:
                // 小于
                return "<";
            case 4:
                // 小于等于
                return "<=";
            case 5:
                // 不等于
                return "!=";
            case 6:
                // 属于
                return "not in";
            case 7:
                // 不属于
                return "in";
            default:
        }

        return null;
    }
}
